﻿CREATE PROCEDURE [sp_ssiscatalog].[get_events_with_duration]
		@execution_id		INT		
AS
BEGIN
		WITH	[all_executables] AS (
				SELECT	em.[operation_id] AS [execution_id],em.[execution_path],SUBSTRING(em.[execution_path],1,LEN(em.[execution_path]) - CHARINDEX('\',REVERSE(em.[execution_path]),1)) AS [parent_execution_path],ISNULL(e.[environment_name],'(none)') AS [environment_name],e.[folder_name],e.[project_name]
				FROM	(	SELECT [operation_id],[execution_path] FROM [$(SSISDB)].[catalog].[event_messages] em WHERE em.[operation_id] = @execution_id
							UNION
							SELECT [execution_id],[execution_path] FROM [$(SSISDB)].[catalog].[executable_statistics] es WHERE es.[execution_id] = @execution_id) em
				INNER JOIN [$(SSISDB)].[catalog].[executions] e
					ON	em.[operation_id] = e.[execution_id]
		)
		SELECT	[execution_id], [execution_path], [parent_execution_path], [environment_name], [folder_name], [project_name]
		INTO	#all_executables
		FROM	[all_executables];
		WITH	[execution_start_time] AS (
				SELECT	e.[start_time]
				FROM	[$(SSISDB)].[catalog].[executions] e
				WHERE	e.[execution_id] = @execution_id
		)
		,[exec_stats] AS (
				SELECT	em.[execution_path],es.[execution_duration],es.[execution_result],es.[start_time],es.[end_time],SUBSTRING(em.[execution_path],1,LEN(em.[execution_path]) - CHARINDEX('\',REVERSE(em.[execution_path]),1)) AS [parent_execution_path],em.[environment_name],em.[folder_name],em.[project_name]
				FROM	#all_executables em
				LEFT OUTER JOIN [$(SSISDB)].[catalog].[executable_statistics] es
					ON	em.[execution_path]	=	es.[execution_path]
					AND em.[execution_id]	=	es.[execution_id]
		)
		,[complete_exec_stats] AS (
				--if an execution fails unexpectedly then not all executables will be in [executable_statistics] - so we have to invent them by getting them from a descendant container!
				SELECT	es.[execution_path],
						es.[parent_execution_path],
						es.[environment_name],
						es.[folder_name],
						es.[project_name],
						CAST(0 AS BIT) AS [GeneratedExecutable],
						MIN(es_descendants.[start_time]) AS [start_time],
						MAX(es_descendants.[end_time]) AS [end_time],
						ISNULL(es.[execution_result],3) AS [execution_result],  --We don't know what happened, so set to something other than "Success|, "Failure" or "Completed"
						COALESCE(es.[execution_duration],DATEDIFF(ms,MIN(es_descendants.[start_time]),MAX(es_descendants.[end_time])),0) AS [execution_duration]
				FROM	[exec_stats] es
				LEFT OUTER JOIN [exec_stats] es_descendants
					ON	es.[execution_path] = SUBSTRING(es_descendants.[execution_path],1,LEN(es.[execution_path]))
				GROUP BY es.[execution_path],es.[parent_execution_path],es.[environment_name],es.[folder_name],es.[execution_result],es.[execution_duration],es.[project_name]
		)--select * from [complete_exec_stats]
		,		[generated_executables] AS (
				SELECT	[new_executable].[execution_path],[new_executable].[parent_execution_path]
						,ISNULL(MIN([new_executable].[start_time]),est.[start_time]) AS [start_time]
						,ISNULL(MAX([new_executable].[end_time]),est.[start_time]) AS [end_time]
						,ISNULL(DATEDIFF(ms,MIN([new_executable].[start_time]),MAX([new_executable].[end_time])),0) AS [execution_duration]
						,MAX([new_executable].[execution_result]) AS [execution_result],[new_executable].[environment_name],[new_executable].[folder_name],[new_executable].[project_name]
				FROM	(
						SELECT	 SUBSTRING([loop_iteration].[execution_path] ,1, [loop_iteration].length_exec_path - [loop_iteration].[char_index_close_square] + 1) as [execution_path]
								,SUBSTRING([loop_iteration].[execution_path] ,1, [loop_iteration].length_exec_path - [loop_iteration].[char_index_open_square]) as [parent_execution_path]
								,[loop_iteration].[start_time],[loop_iteration].[end_time],[loop_iteration].execution_duration,[loop_iteration].[execution_result],[loop_iteration].[environment_name],[loop_iteration].[folder_name],[loop_iteration].[project_name]
						FROM	(
								SELECT	[execution_path],CHARINDEX('[',REVERSE([execution_path]),1) [char_index_open_square],CHARINDEX(']',REVERSE([execution_path]),1) [char_index_close_square],LEN([execution_path]) [length_exec_path],es.[start_time],es.[end_time],es.execution_duration,es.[execution_result],es.[environment_name],es.[folder_name],es.[project_name]
								FROM	[complete_exec_stats] es
								WHERE	execution_path LIKE '%\[%]%'  ESCAPE '\'
								)AS [loop_iteration]
						) AS [new_executable]
				CROSS JOIN [execution_start_time] est
				GROUP	BY [new_executable].[execution_path],[new_executable].[parent_execution_path],[new_executable].[environment_name],[new_executable].[folder_name],[new_executable].[project_name],est.[start_time]
		)
		,		[anchor] AS (
				SELECT	CAST('' AS nvarchar(max)) as [parent_execution_path],es.[execution_path],es.[execution_duration],es.[execution_result],es.[start_time],es.[end_time],es.[environment_name],es.[folder_name],es.[project_name],es.[GeneratedExecutable]
				FROM	[complete_exec_stats] es
				WHERE	CHARINDEX('\',es.[execution_path],2) = 0 --  <-Root executable (i.e. the executed package)
				UNION	ALL
				SELECT	ge.[parent_execution_path],ge.[execution_path],ge.[execution_duration],ge.[execution_result],ge.[start_time],ge.[end_time],ge.[environment_name],ge.[folder_name],ge.[project_name],CAST(1 AS BIT) AS [GeneratedExecutable]
				FROM	[generated_executables] ge
		)
		,		[exec_stats_recursed] AS (
				SELECT	[parent_execution_path],es.[execution_path],es.[execution_duration],es.[execution_result],es.[start_time],es.end_time,es.[environment_name],es.[folder_name],es.project_name,es.[GeneratedExecutable]
				FROM	[anchor] es
				UNION ALL
				SELECT	es.[parent_execution_path],es.[execution_path],es.[execution_duration],es.[execution_result],ISNULL(es.[start_time],esr.[start_time]) AS [start_time],ISNULL(es.end_time,esr.[end_time]) AS [end_time],es.[environment_name],es.[folder_name],es.[project_name],es.[GeneratedExecutable]
				FROM	[complete_exec_stats] es
					INNER JOIN [exec_stats_recursed] esr			ON	es.[parent_execution_path]		=	esr.[execution_path]
		)
		,	[recursed_with_level] AS (
				SELECT	LEN(es.[execution_path]) - LEN(REPLACE(es.[execution_path],'\','')) + LEN(es.[execution_path]) - LEN(REPLACE(es.[execution_path],'[','')) - 1 AS [Level]
						,es.[execution_path],es.[parent_execution_path],es.[execution_duration],es.[execution_result],es.[start_time],es.[end_time],es.[environment_name],es.[folder_name],es.[project_name],es.[GeneratedExecutable]
				FROM	[exec_stats_recursed] es
		)
		,	[es_with_numberofdescendants] AS (
				SELECT	es.[Level],es.[execution_path],es.[parent_execution_path],es.[execution_duration],es.[execution_result],es.[start_time],es.[end_time]
						,es.[environment_name],es.[folder_name],es.[project_name],es.[GeneratedExecutable]
						--,COUNT([es_descendants].[execution_path])	AS [NumberOfDescendantContainers]
				FROM	[recursed_with_level] es
				--	LEFT OUTER JOIN [recursed_with_level] [es_descendants]		ON	es.[execution_path]	=	SUBSTRING([es_descendants].[parent_execution_path],1,LEN(es.[execution_path]))
				--GROUP	BY es.[Level],es.[execution_path],es.[parent_execution_path],es.[execution_duration],es.[execution_result],es.[start_time],es.[end_time],es.[environment_name],es.[folder_name],es.[project_name],es.[GeneratedExecutable]
		)
		,	[es_with_numberofchildren] AS (
				SELECT	 [es_with_numberofdescendants].[Level]
						,[es_with_numberofdescendants].[execution_path]			AS [container_execution_path]
						,[es_with_numberofdescendants].[parent_execution_path]	AS [container_parent_execution_path]
						,[es_with_numberofdescendants].[execution_duration]		AS [container_execution_duration]
						,[es_with_numberofdescendants].[execution_result]		AS [container_execution_result]
						,[es_with_numberofdescendants].[start_time]				AS [container_start_time]
						,[es_with_numberofdescendants].[environment_name]
						,[es_with_numberofdescendants].[folder_name]
						,[es_with_numberofdescendants].[project_name]
						,[es_with_numberofdescendants].[GeneratedExecutable]
						,CONVERT(CHAR(30),[es_with_numberofdescendants].[start_time],120)				AS [container_start_time_text]
						,[es_with_numberofdescendants].[end_time]				AS [container_end_time]
						,CONVERT(CHAR(30),[es_with_numberofdescendants].[end_time],120)				AS [container_end_time_text]
						--,[NumberOfDescendantContainers]
						--,COUNT([es_children].[execution_path])					AS [NumberOfChildContainers]
						,cj.[start_time]										AS [execution_start_time]
						,cj.[end_time]											AS [execution_end_time]
						,cj.[execution_duration]								AS [execution_duration]
						,DATEDIFF(ms,cj.[start_time],[es_with_numberofdescendants].[start_time])		AS [pre_container_execution_duration]
						,cj.[execution_duration] - [es_with_numberofdescendants].[execution_duration] - 
							DATEDIFF(ms,cj.[start_time],[es_with_numberofdescendants].[start_time])		AS [post_container_execution_duration]
				FROM	[es_with_numberofdescendants]
				--LEFT OUTER JOIN [recursed_with_level] [es_children]			ON	[es_with_numberofdescendants].[execution_path]	=	[es_children].[parent_execution_path]
				CROSS JOIN (
						SELECT	es.[execution_duration],es.[start_time],es.[end_time]
						FROM	[exec_stats_recursed] es
						--WHERE	es.[execution_path] = @RootContainer
						) cj
				--WHERE	SUBSTRING([es_with_numberofdescendants].[execution_path],1,LEN(@RootContainer)) = @RootContainer
				--GROUP	BY [es_with_numberofdescendants].[Level],[es_with_numberofdescendants].[execution_path],[es_with_numberofdescendants].[parent_execution_path],[es_with_numberofdescendants].[execution_duration],[es_with_numberofdescendants].[execution_result],[es_with_numberofdescendants].[start_time],[es_with_numberofdescendants].[environment_name],[es_with_numberofdescendants].[folder_name],[es_with_numberofdescendants].[project_name]
				--		,[es_with_numberofdescendants].[GeneratedExecutable],[es_with_numberofdescendants].[end_time],[NumberOfDescendantContainers],cj.[end_time],cj.[start_time],cj.[execution_duration]
				)
		,		[es_with_concurrentexecs] AS (
				SELECT	 [es].[Level]
						,[es].[container_execution_path]
						,[es].[container_parent_execution_path]
						,[es].[container_execution_duration]
						,[es].[container_execution_result]
						,[es].[container_start_time]
						,[es].[container_start_time_text]
						,[es].[container_end_time]
						,[es].[container_end_time_text]
						--,[es].[NumberOfDescendantContainers]
						--,[es].[NumberOfChildContainers]
						,[es].[execution_start_time]
						,[es].[execution_end_time]
						,[es].[execution_duration]
						,[es].[environment_name]
						,[es].[folder_name]
						,[es].[project_name]
						,[es].[GeneratedExecutable]
						,[es].[pre_container_execution_duration]
						,[es].[post_container_execution_duration]
						,[es].[container_name]
						--,[cc_execs].[execution_path] AS [concurrent_container]
				FROM	(
						SELECT	 [es_with_numberofchildren].[Level]
								,[es_with_numberofchildren].[container_execution_path]
								,[es_with_numberofchildren].[container_parent_execution_path]
								,[es_with_numberofchildren].[container_execution_duration]
								,[es_with_numberofchildren].[container_execution_result]
								,[es_with_numberofchildren].[container_start_time]
								,[es_with_numberofchildren].[container_start_time_text]
								,[es_with_numberofchildren].[container_end_time]
								,[es_with_numberofchildren].[container_end_time_text]
								--,[es_with_numberofchildren].[NumberOfDescendantContainers]
								--,[es_with_numberofchildren].[NumberOfChildContainers]
								,[es_with_numberofchildren].[execution_start_time]
								,[es_with_numberofchildren].[execution_end_time]
								,[es_with_numberofchildren].[execution_duration]
								,[es_with_numberofchildren].[environment_name]
								,[es_with_numberofchildren].[folder_name]
								,[es_with_numberofchildren].[project_name]
								,[es_with_numberofchildren].[GeneratedExecutable]
								/*Currently a bug which means start times of containers could be earlier than an ancestor - thus causing -ve durations here - which we have to workaround. Bug is expected to be fixed before Denali RTM*/
								,CASE WHEN [es_with_numberofchildren].[pre_container_execution_duration] < 0 THEN 0 ELSE [es_with_numberofchildren].[pre_container_execution_duration] END AS [pre_container_execution_duration]
								,CASE WHEN [es_with_numberofchildren].[post_container_execution_duration] < 0 THEN 0 ELSE [es_with_numberofchildren].[post_container_execution_duration] END AS [post_container_execution_duration]
								,SUBSTRING([es_with_numberofchildren].[container_execution_path],LEN([es_with_numberofchildren].[container_execution_path]) - CHARINDEX('\',REVERSE([es_with_numberofchildren].[container_execution_path]),0) + 2,LEN([es_with_numberofchildren].[container_execution_path])) AS [container_name]
						FROM	[es_with_numberofchildren]
						) AS [es]
				--LEFT OUTER JOIN [es_with_numberofdescendants] cc_execs		ON	(		(	[es].[container_start_time]	<=	[cc_execs].[start_time]	AND	[cc_execs].[start_time]	<=	[es].[container_end_time]) --[cc_execs] = concurrent executables
				--									OR	(	[es].[container_start_time]	<=	[cc_execs].[end_time]	AND	[cc_execs].[end_time]	<=	[es].[container_end_time])
				--									OR	(	[cc_execs].[start_time]		<=	[es].[container_start_time]	AND	[es].[container_end_time]	<=	[cc_execs].[end_time])
				--															)
				--														AND	[cc_execs].[NumberOfDescendantContainers] = 0
				--														AND	[cc_execs].[execution_path] <> [es].[container_execution_path]
				)
		SELECT	 [es_with_concurrentexecs].[Level]
				,[es_with_concurrentexecs].[container_execution_path]
				,[es_with_concurrentexecs].[container_parent_execution_path]
				,[es_with_concurrentexecs].[container_execution_duration]
				,[es_with_concurrentexecs].[container_execution_result]
				,CASE	WHEN [es_with_concurrentexecs].[container_execution_result] = 0 THEN 'Success'
					WHEN [es_with_concurrentexecs].[container_execution_result] = 1 THEN 'Failure'
					WHEN [es_with_concurrentexecs].[container_execution_result] = 2 THEN 'Completion'
					ELSE 'Cancelled'
				 END AS [container_execution_result_text]
				,[es_with_concurrentexecs].[container_start_time]
				,[es_with_concurrentexecs].[container_start_time_text]
				,[es_with_concurrentexecs].[container_end_time]
				,[es_with_concurrentexecs].[container_end_time_text]
				--,[es_with_concurrentexecs].[NumberOfDescendantContainers]
				--,[es_with_concurrentexecs].[NumberOfChildContainers]
				,[es_with_concurrentexecs].[execution_start_time]
				,[es_with_concurrentexecs].[execution_end_time]
				,[es_with_concurrentexecs].[execution_duration]
				,[es_with_concurrentexecs].[environment_name]
				,[es_with_concurrentexecs].[folder_name]
				,[es_with_concurrentexecs].[project_name]
				,[es_with_concurrentexecs].[GeneratedExecutable]
				,[es_with_concurrentexecs].[pre_container_execution_duration]
				,[es_with_concurrentexecs].[post_container_execution_duration]
				,[es_with_concurrentexecs].[container_name]
				--,STUFF(	(	SELECT	',' + CHAR(10) + CASE WHEN e.[concurrent_container] IS NULL THEN '' ELSE e.[concurrent_container] END
				--			FROM	[es_with_concurrentexecs] e
				--			WHERE	e.[container_execution_path]	=	[es_with_concurrentexecs].[container_execution_path]
				--			FOR XML PATH('')
				--		),1,1,'')	AS	[ConcurrentExecutables]
				--,SUM(CASE WHEN [es_with_concurrentexecs].[concurrent_container] IS NULL THEN 0 ELSE 1 END) AS [CountOfConcurrentExecutables]
				,CAST(IIF(ecp.[execution_path] IS NULL, 0,1) AS BIT) AS [phases_have_been_logged]
		FROM	[es_with_concurrentexecs]
		LEFT OUTER JOIN (
				SELECT	DISTINCT ecp.[execution_path]
				FROM	[$(SSISDB)].[catalog].[execution_component_phases] ecp
				WHERE	ecp.[execution_id] = @execution_id
				)ecp
			ON	[es_with_concurrentexecs].[container_execution_path] = ecp.[execution_path]
		GROUP	BY
				 [es_with_concurrentexecs].[Level]
				,[es_with_concurrentexecs].[container_execution_path]
				,[es_with_concurrentexecs].[container_parent_execution_path]
				,[es_with_concurrentexecs].[container_execution_duration]
				,[es_with_concurrentexecs].[container_execution_result]
				,[es_with_concurrentexecs].[container_start_time]
				,[es_with_concurrentexecs].[container_start_time_text]
				,[es_with_concurrentexecs].[container_end_time]
				,[es_with_concurrentexecs].[container_end_time_text]
				--,[es_with_concurrentexecs].[NumberOfDescendantContainers]
				--,[es_with_concurrentexecs].[NumberOfChildContainers]
				,[es_with_concurrentexecs].[execution_start_time]
				,[es_with_concurrentexecs].[execution_end_time]
				,[es_with_concurrentexecs].[execution_duration]
				,[es_with_concurrentexecs].[environment_name]
				,[es_with_concurrentexecs].[folder_name]
				,[es_with_concurrentexecs].[project_name]
				,[es_with_concurrentexecs].[GeneratedExecutable]
				,[es_with_concurrentexecs].[pre_container_execution_duration]
				,[es_with_concurrentexecs].[post_container_execution_duration]
				,[es_with_concurrentexecs].[container_name]
				,ecp.[execution_path]
END